package com.yuren;

import com.google.common.collect.Lists;
import com.yuren.entity.ColumnEntity;
import org.apache.poi.xwpf.usermodel.*;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTTblWidth;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.STTblWidth;
import org.springframework.util.StringUtils;

import javax.swing.filechooser.FileSystemView;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigInteger;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.stream.Collectors;

/**
 * @author zzw
 * @date 2021/12/1 11:38
 */
public class TableBuilder {

    /**
     * 属性配置
     */
    private static Properties props = new Properties();

    static {
        try {
            // 加载配置文件 读取外部配置文件
            InputStream is = TableBuilder.class.getClassLoader().getResourceAsStream("application.properties");

            // 创建Properties对象
            props.load(is);
            // 加载数据库驱动
            Class.forName(props.getProperty("driver"));


        } catch (Exception e){
            e.printStackTrace();
        }
    }

    public static void builder() {
        // 连接数据库
        try{
            // 获取数据库连接
            Connection conn = DriverManager.getConnection(props.getProperty("url")+"information_schema",props.getProperty("uname"),props.getProperty("pwd"));
            // 获取application.properties文件中的库名
            String tableSchemas = Lists.newArrayList(props.getProperty("table_schema").split(",")).stream().map(item->"'"+item+"'").collect(Collectors.joining(","));
            // 获取application.properties文件中的表名
            String tableNames = Lists.newArrayList(props.getProperty("d_jail_base"),props.getProperty("d_enforce")
                    ,props.getProperty("d_logistics"),props.getProperty("d_security")
                    ,props.getProperty("d_general"),props.getProperty("d_iot"),props.getProperty("general_workflow")).stream().collect(Collectors.joining(","));
            tableNames = Lists.newArrayList(tableNames.split(",")).stream().map(item->"'"+item+"'").collect(Collectors.joining(","));

            Statement stat = conn.createStatement();

            StringBuilder sqlSb = new StringBuilder();
            sqlSb.append(" SELECT col.TABLE_NAME, col.ORDINAL_POSITION,  col.COLUMN_NAME, col.COLUMN_TYPE, col.IS_NULLABLE, col.COLUMN_COMMENT,");
            sqlSb.append(" tab.TABLE_COMMENT,tab.TABLE_SCHEMA  FROM INFORMATION_SCHEMA.COLUMNS col ");
            sqlSb.append(" INNER JOIN INFORMATION_SCHEMA.TABLES tab ON col.TABLE_NAME = tab.TABLE_NAME and col.table_schema = tab.table_schema");
            sqlSb.append(" WHERE col.table_schema in").append("(").append(tableSchemas).append(")");
            sqlSb.append(" AND col.table_name in").append("(").append(tableNames).append(")");
            sqlSb.append(" AND tab.TABLE_COMMENT <> 'VIEW'");
            ResultSet rs = stat.executeQuery(sqlSb.toString());
            List<ColumnEntity> columnEntityList = Lists.newArrayList();
            while (rs.next()){
                ColumnEntity columnEntity = new ColumnEntity();
                // 序号
                columnEntity.setColumnOrder(rs.getInt("ORDINAL_POSITION"));
                // 字段
                columnEntity.setColumnName( rs.getString("COLUMN_NAME"));
                // 字段类型
                columnEntity.setColumnType(rs.getString("COLUMN_TYPE"));
                // 是否允许为空
                columnEntity.setIsNullisNullable( rs.getString("IS_NULLABLE"));
                // 字段注释
                columnEntity.setColumnComment(rs.getString("COLUMN_COMMENT"));
                // 表名
                columnEntity.setTableName(rs.getString("TABLE_NAME"));
                // 表注释
                columnEntity.setTableComment(rs.getString("TABLE_COMMENT"));
                // 库名
                columnEntity.setTableSchema( rs.getString("TABLE_SCHEMA"));
                columnEntityList.add(columnEntity);
            }
            rs.close();
            stat.close();
            conn.close();
            // 结果根据表名分组
            Map<String, List<ColumnEntity>> tableCollect = columnEntityList.stream().collect(Collectors.groupingBy(ColumnEntity::getTableName));
            // 库名集合
            List<String> tableSchemaList = Lists.newArrayList(props.getProperty("table_schema").split(","));

            write2Docx(tableCollect,tableSchemaList);
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    /**
     * 生成.docx格式word文档
     * @param tableCollect
     * @param tableSchemaList
     * @throws IOException
     */
    private static void write2Docx(Map<String, List<ColumnEntity>> tableCollect,List<String> tableSchemaList) throws IOException {
        File desktopDir = FileSystemView.getFileSystemView() .getHomeDirectory();
        String desktopPath = desktopDir.getAbsolutePath()+ "\\table.docx";
        FileOutputStream out = new FileOutputStream(new File(desktopPath));
        // 添加标题
        XWPFDocument document= new XWPFDocument();
        XWPFParagraph titleParagraph = document.createParagraph();
        // 设置段落居中
        titleParagraph.setAlignment(ParagraphAlignment.CENTER);
        XWPFRun titleParagraphRun = titleParagraph.createRun();
        titleParagraphRun.setText("数据库设计文档");
        titleParagraphRun.setColor("000000");
        titleParagraphRun.setFontSize(20);

        // 遍历数据库集合
        for(String tableSchema:tableSchemaList){
            // 数据库段落
            XWPFParagraph tableSchemaParagraph = document.createParagraph();
            XWPFRun tableSchemaRun = tableSchemaParagraph.createRun();
            tableSchemaRun.setText(tableSchema+"库:");
            tableSchemaRun.setColor("000000");
            tableSchemaRun.setFontSize(16);
            if(StringUtils.isEmpty(props.getProperty(tableSchema))) continue;
            // 根据库名取数据表集合
            List<String> tableNameList= Lists.newArrayList(props.getProperty(tableSchema).split(","));

            // 遍历数据表集合
            for (String tableName: tableNameList) {
                // 获取表结构
                List<ColumnEntity> columnEntitys = tableCollect.get(tableName);
                // 数据表段落
                XWPFParagraph tableNameParagraph = document.createParagraph();
                XWPFRun tableNameRun = tableNameParagraph.createRun();
                tableNameRun.setText(tableName+" "+columnEntitys.get(0).getTableComment());
                tableNameRun.setColor("000000");
                tableNameRun.setFontSize(14);

                // 基本信息表格
                XWPFTable infoTable = document.createTable();
                // 列宽自动分割
                CTTblWidth infoTableWidth = infoTable.getCTTbl().addNewTblPr().addNewTblW();
                infoTableWidth.setType(STTblWidth.DXA);
                infoTableWidth.setW(BigInteger.valueOf(9072));
                // 表格第一行
                XWPFTableRow infoTableRowOne = infoTable.getRow(0);
                infoTableRowOne.getCell(0).setText("序号");
                infoTableRowOne.addNewTableCell().setText("字段名称");
                infoTableRowOne.addNewTableCell().setText("字段类型");
                infoTableRowOne.addNewTableCell().setText("是否允许为空");
                infoTableRowOne.addNewTableCell().setText("中文描述");
                // 表格内容
                for(ColumnEntity columnEntity:columnEntitys){
                    XWPFTableRow infoTableRowTwo = infoTable.createRow();
                    infoTableRowTwo.getCell(0).setText(String.valueOf(columnEntity.getColumnOrder()));
                    infoTableRowTwo.getCell(1).setText(columnEntity.getColumnName());
                    infoTableRowTwo.getCell(2).setText(columnEntity.getColumnType());
                    infoTableRowTwo.getCell(3).setText(columnEntity.getIsNullisNullable());
                    infoTableRowTwo.getCell(4).setText(columnEntity.getColumnComment());
                }
            }
        }
        document.write(out);
        out.close();
        System.out.println("生成word文件成功,文件路径："+desktopPath);
    }
}
